package whf.framework.tools.generator.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.lang.StringUtils;

import whf.framework.log.Log;
import whf.framework.log.LogFactory;
import whf.framework.resource.db.ConnectionManager;

/**
 * @author wanghaifeng
 * @create Nov 2, 2006 9:50:47 AM
 * 
 */
public class OracleTable extends Table {
	private static Log log = LogFactory.getLog(OracleTable.class);
	
	public OracleTable(ConnectionManager cm,String tableSchema, String name, String tablePre, String tableComment) throws Exception{
		super(cm, tableSchema, name, tablePre, tableComment);
		log.debug("初始化数据库表及其列属性等！");
		String sql = "SELECT t1.data_type, t1.column_name,t2.comments as column_comment,t1.nullable as is_nullable,t1.char_length as character_maximum_length "
			+"FROM user_tab_columns t1, user_col_comments t2 WHERE t1.table_name=t2.table_name AND t1.column_name=t2.COLUMN_name AND t1.table_name=? ORDER BY T1.COLUMN_ID";
		log.debug(sql);
		log.debug(this.getTableName());
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = cm.getPooledConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, this.getTableName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				String dataType = rs.getString("data_type");
				String columnName = rs.getString("column_name");
				String columnComment = rs.getString("column_comment");
				boolean nullable = "Y".equalsIgnoreCase(rs.getString("is_nullable"));
				int maxLength = rs.getInt("character_maximum_length");
				Column col = new Column(this, columnName, dataType, columnComment, nullable, maxLength);
				log.debug(col);
				this.addColumn(col);
			}
			rs.close();
			pstmt.close();
			sql = "SELECT t2.table_name,t2.column_name,t1.constraint_type, "+
			"(SELECT table_name FROM user_cons_columns WHERE constraint_name=t1.r_constraint_name AND owner=t1.owner) AS r_table_name,"
			+"(SELECT column_name FROM user_cons_columns WHERE constraint_name=t1.r_constraint_name AND owner=t1.owner) AS r_column_name "
			+"FROM user_constraints t1, user_cons_columns t2 "
			+"WHERE t1.owner=t2.owner AND t1.constraint_name=t2.constraint_name "
			+"AND t1.owner=? AND t1.TABLE_NAME=?";
			log.debug(sql);
			log.debug(this.getTableSchema());
			log.debug(this.getTableName());
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, this.getTableSchema());
			pstmt.setString(2, this.getTableName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				String columnName = rs.getString(2);
				String constraintType = rs.getString(3);
				String referenceTableName = rs.getString(4);
				String referenceColumnName = rs.getString(5);
				
				Column col = findColumnByName(columnName);
				if(col != null){
					if(StringUtils.equalsIgnoreCase(constraintType, "P")){
						col.setPrimaryKey(true);
					}else if(StringUtils.equalsIgnoreCase(constraintType,"R")){
						col.setForeign(true);
						col.setReferencedColumn(referenceColumnName);
						col.setReferencedTable(referenceTableName);
					}else if(StringUtils.equalsIgnoreCase("U", constraintType)){
						col.setUnique(true);
					}
				}
			}
		}finally{
			if(rs != null) rs.close();
			if(pstmt != null) pstmt.close();
			if(con != null) con.close();
		}
	}
	
	public static void main(String[] args) throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.20.8:1523:ora92","wlrk_new", "a");
		PreparedStatement pstmt = con.prepareStatement("SELECT t1.data_type, t1.column_name,t2.comments as column_comment,t1.nullable as is_nullable,t1.char_length as character_maximum_length FROM user_tab_columns t1, user_col_comments t2 WHERE t1.table_name=t2.table_name AND t1.column_name=t2.COLUMN_name AND t1.table_name='COL_QUERY' ORDER BY T1.COLUMN_ID");
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			System.out.println(rs.getString(1));
		}
	}
}
